from utils.db import db


class PmsUserInfo:
    @classmethod
    def getReviewerAndTraderByAdviser(self, adviser):
        getOperatorSql = '''
                    select ao.operator_type, ao.operator_user_id from ying99_pms.pms_user_extend_info u
                    join ying99_pms.pms_user_adviser ua on u.pms_user_id = ua.pms_user_id
                    join ying99_pms.pms_adviser_operator ao on ua.adviser_id = ao.adviser_id
                    where ao.operator_type=%s and u.user_account=%s
                    '''
        reviewerIds = db.query(getOperatorSql, ['STRATEGY_REVIEWER', adviser])
        tradeIds = db.query(getOperatorSql, ['TRADER', adviser])
        reviewerUserAccount = \
            db.selectOneTable("ying99_pms.pms_user_extend_info", pms_user_id=reviewerIds[0]['operator_user_id'])[
                0][
                'user_account']
        tradeUserAccount = \
            db.selectOneTable("ying99_pms.pms_user_extend_info", pms_user_id=tradeIds[0]['operator_user_id'])[0][
                'user_account']
        return [reviewerUserAccount, tradeUserAccount]

    @classmethod
    def getAdviserIdByAdviserName(self, userAccount):
        '''根据用户名获取投顾团队id'''
        sql = "select a.adviser_id from ying99_pms.pms_user_extend_info i join ying99_pms.pms_user_adviser a on i.pms_user_id=a.pms_user_id where i.user_account=%s"
        result = db.query(sql, [userAccount])
        return result[0]['adviser_id']

    @staticmethod
    def getPmsUserIdByUserAccount(userAccount):
        result = db.selectOneTable("ying99_pms.pms_user_extend_info", user_account=userAccount)
        return result[0]['pms_user_id']

    @staticmethod
    def get_account_adjust_date(prod_code, limit=10):
        """获取符合账户调仓要求的账户数据"""
        q_sql = "SELECT a.broker_user_id, ca.txn_account_id, ca.po_code FROM ying99_potrade.capital_account ca LEFT JOIN ying99_accounts.account a ON ca.account3_id = a.account3_id AND ca.broker = a.broker LEFT JOIN ying99_icam.advisor_sign_contract b ON ca.txn_account_id = b.txn_account_id WHERE ca.txn_account_id IS NOT NULL AND ca.objective IS NOT NULL AND a.broker_user_id IS NOT NULL AND ca.po_code = %s AND CASE  WHEN json_valid(objective) THEN JSON_EXTRACT(objective, '$.autoFollow') = '1' ELSE NULL END AND b.status = 1 AND ca.capital_account_status = 'ASSET' ORDER BY ca.create_time DESC LIMIT %s;"
        result = db.query(q_sql, [prod_code, limit])
        return result

